#!/usr/bin/env python
# coding:utf-8


from utils.mysqlutils import MysqlUtils
from datatabletransfer import DataTableTransfer
from fieldsmap import *
import logging


class FundNav(DataTableTransfer):
    """
    基金净值(TQ_QT_FDNAV) --> 基金净值(fund_nav)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,NAVDATE as end_date from TQ_QT_FDNAV;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.end_date,'-','') as end_date 
               from fund_nav f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9';'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID, PUBLISHDATE, NAVDATE, CUR, UNITNAV, ACCUNITNAV,
    # GROWLINEVALUE, ASSETNAV,GROWRATE*100 GROWRATE, ISEXDIVDATE, MEMO from TQ_QT_FDNAV limit %s, 1000;''')

    TABLE = 'fund_nav'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundNavMap
    MAP_VALUES = FundNavValueMap


class FundNavCur(DataTableTransfer):
    """
    货币式基金净值(TQ_QT_FDNAVCUR) --> 货币式基金净值(fund_nav_cur)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,ENDDATE as end_date,SPECIALSIGN as special_sign  from TQ_QT_FDNAVCUR;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.end_date,'-','') as end_date ,special_sign
    from fund_nav_cur f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9';'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,PUBLISHDATE,BEGINDATE,ENDDATE,SPECIALSIGN,MILLIONGAIN,
    # SEVENDAYYIELD,MILCUMUGAIN,ASSETNAV,ANNOUNCEDGAIN,UNIT,ISSTAT,MEMO from TQ_QT_FDNAVCUR limit %s, 1000;''')

    TABLE = 'fund_nav_cur'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False, 'special_sign':True}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
        'begin_date': None,
    }

    MAP_FIELDS = FundNavCurMap
    MAP_VALUES = FundNavCurValueMap


class FundNavBenchmark(DataTableTransfer):
    """
    净值增长率与基准比较(TQ_FD_NAVBENMARK) --> 净值增长率与基准比较(fund_nav_benchmark)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,ENDDATE as end_date,REPORTTYPE as report_type,STATPERIOD as  stat_period
    from TQ_FD_NAVBENMARK;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.end_date,'-','') as end_date ,report_type,stat_period
    from fund_nav_benchmark f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9';'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID, PUBLISHDATE, BEGINDATE, ENDDATE, REPORTTYPE,
    # STATPERIOD, NAVGRORATE, NAVGRORATESTA, PERBENMARKRATE, PERBENMARKRATESTA, FIELDDIF, STANDEVIDIF, MEMO
    # from TQ_FD_NAVBENMARK limit %s, 1000;''')

    TABLE = 'fund_nav_benchmark'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'end_date':False, 'report_type':True, 'stat_period':True}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'end_date': None,
        'begin_date': None,
    }

    MAP_FIELDS = FundNavBenchmarkMap
    MAP_VALUES = FundNavBenchmarkValueMap


class FundPlateInfo(DataTableTransfer):
    """
    基金分类(TQ_FD_TYPE) --> 基金所属板块(sec_plate_info)
    证券板块表新增场内场外基金分类(sec_basic_info) --> 基金所属板块(sec_plate_info)
    """

    # 待插入的数据查询SQL
    QUERY_SOURCE = ('''select SECURITYID as sec_uni_code, CLASSCODE as plate_code, 
    BEGINDATE as begin_date from TQ_FD_TYPE where TYPESTYLE in (10,22) ;''')

    QUERY_TARGET = ('''select replace(b.source_id,'FD','') as sec_uni_code, plate_code, replace(begin_date,'-','') as begin_date
    from sec_plate_info p join sec_basic_info b on p.sec_uni_code = b.sec_uni_code  
    where p.status <> '9' and b.sec_type= '1004003' and sec_small_type in(1004003002,1004003003)
    and (p.plate_code like '1002%' or p.plate_code like '1003%');''')

    TABLE = 'sec_plate_info'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code': False, 'begin_date': False, 'plate_code': True}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'end_date': None,
        'begin_date': None,
    }

    MAP_FIELDS = PlateInfoMap
    MAP_VALUES = PlateInfoValueMap


class FundDividend(DataTableTransfer):
    """
    基金分红(TQ_FD_BONUS) --> 基金分红(fund_dividend)
    """
    QUERY_SOURCE = ('''select SECURITYID as sec_uni_code,BONUSYEAR as dividend_year, CURYACCBNSAMT as dividend_serial_number
    from TQ_FD_BONUS ;''')

    QUERY_TARGET = ('''select replace(b.source_id,'FD','') as sec_uni_code,dividend_year, dividend_serial_number
       from fund_dividend f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code  
       where f.status <> '9' ;''')
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID, PUBLISHDATE, DIVIIMPMARKD,DEFSHAREMODE,BONUSYEAR,
    # CURYACCBNSAMT,DISTINCBASEDATE,AVAIDISTINC,ACTBONUSAMT,BASISSHARE,UNITPTAXDEV*10 as UNITPTAXDEV,
    # UNITATAXDEV*10 as UNITATAXDEV,RECORDDATE, OUTRIGHTDATE,INNERRIGHTDATE,ACTRIGHTDATE,OUTDEVDATE,INNERDEVDATE,
    # TURNREINDATE,REINPAYDATE,REINREDBEGDATE,CUR from TQ_FD_BONUS limit %s, 1000;''')

    TABLE = 'fund_dividend'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'dividend_year':False, 'dividend_serial_number':False}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'dividend_base_date': None,
        'record_date': None,
        'out_right_date': None,
        'inner_right_date': None,
        'actual_right_date': None,
        'out_dividend_date': None,
        'inner_dividend_date': None,
        'div_rein_date': None,
        'rein_pay_date': None,
        'rein_redem_beg_date': None,
    }

    MAP_FIELDS = FundDividendMap
    MAP_VALUES = FundDividendValueMap


class MoneyMarketFundDividend(DataTableTransfer):
    """
    货币式基金收益支付(TQ_FD_MDHISTORY) --> 货币式基金分红(fund_money_market_dividend)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,ACCBEGDATE as acc_beg_date from TQ_FD_MDHISTORY;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.acc_beg_date,'-','') as acc_beg_date 
    from fund_money_market_dividend f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9';'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,DECLAREDATE,`YEAR`,`TIME`,PAYMODE,ACCBEGDATE,
    # ACCENDDATE,YIELDPAYDATE,YEILDCARDATE,RECORDDATE,BONUSOFFDAY,REINPAYDATE,REDEBEGDATE
    # from TQ_FD_MDHISTORY limit %s, 1000;''')

    TABLE = 'fund_money_market_dividend'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'acc_beg_date':False}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'acc_beg_date': None,
        'acc_end_date': None,
        'dividend_pay_date': None,
        'dividend_carry_date': None,
        'record_date': None,
        'dividend_off_date': None,
        'rein_pay_date': None,
        'rein_redem_beg_date': None,
    }

    MAP_FIELDS = MoneyMarketFundDividendMap
    MAP_VALUES = MoneyMarketFundDividendValueMap


class FundSplitConversion(DataTableTransfer):
    """
    基金拆分(TQ_FD_SPLIT)	--> 基金拆分折算(fund_split_conversion)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,SPLITDATE as split_conversion_date from TQ_FD_SPLIT;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.split_conversion_date,'-','') as split_conversion_date
    from fund_split_conversion f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9';'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD', SECURITYID) as SECURITYID,PUBLISHDATE,DIVITYPE,DIVIIMPMARKD,RECORDCHGDATE,
    # SPLITDATE, ESPLITRATIO/10 as ESPLITRATIO,SPLITRATIO/10 as SPLITRATIO,BESPLITSH,AFSPLITSH,BESPLITNAV,AFSPLITNAV,
    # BESPLITACCNAV,AFSPLITACCNAV,DISBENCHDATE,ISBONUS,CUR,SPLITSYEAR,CURYACCSLSAMT,MEMO
    # from TQ_FD_SPLIT limit %s, 1000;''')

    TABLE = 'fund_split_conversion'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'split_conversion_date':False}   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'split_conversion_date': None,
        'share_change_record_date': None,
        'dividend_base_date': None,
    }

    MAP_FIELDS = FundSplitConversionMap
    MAP_VALUES = FundSplitConversionValueMap


class FundISplitConversion(DataTableTransfer):
    """
    创新型基金折算表(TQ_FD_FUNDICONVERT) --> 创新型基金拆分折算(fund_innovate_split_conversion)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,CONVERTDATE as split_conversion_date from TQ_FD_FUNDICONVERT;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code, replace(f.split_conversion_date,'-','') as split_conversion_date
    from fund_innovate_split_conversion f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9';'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) SECURITYID,SYMBOL,DECLAREDATE,UPDATEDATE,CONVERTDATE,CONVERTCLASS,
    # NEWCONVERT,BCVTS,BCVTSOTC,BCVTSEXC,ACVTS,ACVTSOTC,ACVTSEXC,BNAV,BNAVCUML,ANAV,ANAVCUML,VALEXCEED,
    # concat('FD',FSCODE) FSCODE,FSSYMBOL,SENDNEWR,SENDNEWROTC,SENDNEWREXC,BSENDS,BSENDSOTC,BSENDSEXC,ASENDS,ASENDSOTC,
    # ASENDSEXC,GIFTS,GIFTSOTC,GIFTSEXC,BSENDNAV,BSENDNAVCUML,ASENDNAV,ASENDNAVCUML,SHARESCALE,MEMO
    # from TQ_FD_FUNDICONVERT limit %s, 1000;''')

    TABLE = 'fund_innovate_split_conversion'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'split_conversion_date':False}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'split_conversion_date': None,
        'update_date': None,
    }

    MAP_FIELDS = FundISplitConversionMap
    MAP_VALUES = FundISplitConversionValueMap


if __name__ == '__main__':

    log_path = '../logs/export_funds_nav.log'
    logging.basicConfig(filename=log_path,
                        format='%(asctime)s %(name)s %(levelname)s [line %(lineno)s]: %(message)s',
                        filemode='w',
                        level=logging.INFO)
    # FundNav.work()
    # FundNavCur.work()
    # FundNavBenchmark.work()
    # FundPlateInfo.work()
    # FundDividend.work()
    # MoneyMarketFundDividend.work()
    # FundSplitConversion.work()
    FundISplitConversion.work()
